/**
 * Copyright 2014 Viettel Telecom. All rights reserved.
 * VIETTEL PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */
package com.viettel.core.dao.cms;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;

import com.viettel.core.common.utils.StringUtil;
import com.viettel.core.entities.cms.GroupCategory;
import com.viettel.core.entities.vo.GroupCategorySearchFilter;
import com.viettel.core.entities.vo.GroupCategorySearchVO;
import com.viettel.core.entities.vo.GroupCategoryVO;
import com.viettel.core.enumtype.ArticleCategoryStatus;
import com.viettel.core.enumtype.GroupCategoryStatus;
import com.viettel.core.exceptions.DataAccessException;
import com.viettel.core.repo.IRepository;

/**
 * Mo ta muc dich cua lop (interface)
 * 
 * @author: TOANTM1
 * @version:
 * @since: 27/02/2014 - 10:50:59
 */
public class GroupCategoryDAOImpl implements GroupCategoryDAO {
private IRepository repo; 
	
 	public void setRepo(IRepository repo) {
		this.repo = repo;
	}

	/**
	 * @author TOANTM1 27/02/2014 - 10:50:59 For:
	 * @description:
	 */
	@Override
	public GroupCategory getGroupCategoryById(Integer id)
			throws DataAccessException {
		return repo.getEntityById(GroupCategory.class, id);
	}

	/**
	 * @author TOANTM1 27/02/2014 - 10:50:59 For:
	 * @description:
	 */
	@Override
	public GroupCategory createGroupCategory(GroupCategory groupcategory)
			throws DataAccessException {
		if (groupcategory == null) {
			throw new IllegalArgumentException("groupcategory");
		}
		repo.create(groupcategory);
		return repo.getEntityById(GroupCategory.class, groupcategory.getId());
	}

	/**
	 * @author TOANTM1 27/02/2014 - 10:50:59 For:
	 * @description:
	 */
	@Override
	public void updateGroupCategory(GroupCategory groupcategory)
			throws DataAccessException {
		if (groupcategory == null) {
			throw new IllegalArgumentException("groupcategory");
		}
		repo.update(groupcategory);
	}

	/**
	 * @author TOANTM1 27/02/2014 - 10:50:59 For:
	 * @description:
	 */
	@Override
	public void deleteGroupCategory(GroupCategory groupcategory)
			throws DataAccessException {
		if (groupcategory == null) {
			throw new IllegalArgumentException("groupcategory");
		}
		repo.delete(groupcategory);
	}

	@Override
	public List<GroupCategory> getAllGroupCategory() throws DataAccessException {
		// TODO Auto-generated method stub
		String sql = "select * from group_category where status != -1 order by LOWER(name) ";

		return repo.getListBySQL(GroupCategory.class, sql, null);
	}

	@Override
	public List<GroupCategory> getListGroupCategoryByParentId(Integer parentId)
			throws DataAccessException {
		List<Object> params = new ArrayList<Object>();
		String sql = "SELECT * FROM group_category  where status != ? AND parent_id = ?  ORDER BY LOWER(name) ";

		params.add(GroupCategoryStatus.DELETED.getValue());
		params.add(parentId);

		return repo.getListBySQL(GroupCategory.class, sql, params);
	}

	@Override
	public List<GroupCategory> getListParentGroupCategory(Integer parentId)
			throws DataAccessException {
		List<Object> params = new ArrayList<Object>();
		String sql = "select * from group_category where status != ? AND parent_id is null ";

		params.add(GroupCategoryStatus.DELETED.getValue());

		if (parentId != null) {
			sql += " and id != ? ";
			params.add(parentId);
		}
		sql += " ORDER BY LOWER(name) ";
		return repo.getListBySQL(GroupCategory.class, sql, params);
	}

	/**
	 * @author tuantm20
	 * @throws DataAccessException
	 */
	@Override
	public List<GroupCategorySearchVO> getListGroupCategoryByFilter(
			GroupCategorySearchFilter filter) throws DataAccessException {// TODO
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();

		selectSql.append(" SELECT gc.id  	as id, ");
		selectSql.append(" gc.name 			as name, ");
		selectSql.append(" gc.code 			as code, ");
		selectSql.append(" gc.description 	as description, ");
		selectSql.append(" gc.status 		as status, ");
		selectSql.append(" gc.media_id 		as mediaId, ");
		selectSql.append(" gc.parent_id 	as parentId, ");
		selectSql.append(" me.name 			as mediaName, ");
		selectSql.append(" gcp.name 		as parentName ");

		fromQuery.append(" FROM group_category gc ");
		fromQuery.append(" 		LEFT JOIN group_category gcp ");
		fromQuery.append(" 			ON gc.parent_id = gcp.id ");
		fromQuery.append(" 		LEFT JOIN Media me ");
		fromQuery.append(" 			ON gc.media_id = me.id ");

		fromQuery.append(" WHERE gc.status != ? ");
		paramSelect.add(GroupCategoryStatus.DELETED.getValue());
		countParams.add(GroupCategoryStatus.DELETED.getValue());

		if (filter.getId() != null) {
			fromQuery.append(" and gc.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}

		if (!StringUtil.isNullOrEmpty(filter.getNameText())) {
			fromQuery
					.append(" and (upper(gc.name_text) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText())
					+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText())
					+ "%");
		}

		if (filter.getCode() != null) {
			fromQuery.append(" and (upper(gc.code) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getCode())
					+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getCode())
					+ "%");
		}

		if (!StringUtil.isNullOrEmpty(filter.getDescription())) {
			fromQuery
					.append(" and (upper(gc.description) like upper(?) ESCAPE '/' )");
			paramSelect.add("%"
					+ StringUtil.toSearchText(filter.getDescription()) + "%");
			countParams.add("%"
					+ StringUtil.toSearchText(filter.getDescription()) + "%");
		}

		if (filter.getStatus() != null) {
			fromQuery.append(" and gc.status = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}

		if (filter.getMediaId() != null) {
			fromQuery.append(" and gc.media_id = ? ");
			paramSelect.add(filter.getMediaId());
			countParams.add(filter.getMediaId());
		}

		if (filter.getParentId() != null) {
			fromQuery.append(" and gc.parent_id = ? ");
			paramSelect.add(filter.getParentId());
			countParams.add(filter.getParentId());
		}
		
		selectSql.append(fromQuery).append(" ORDER BY gc.id DESC ");

		countQuery.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);

		String[] fieldNames = new String[] { "id", "name", "code",
				"description", "status", "mediaId", "parentId", "mediaName",
				"parentName" };

		Type[] fieldTypes = new Type[] { StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.STRING, StandardBasicTypes.INTEGER,
				StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING };

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(GroupCategory.class);

		List<GroupCategorySearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					GroupCategorySearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(GroupCategorySearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}

		return list;
	}

	/**
	 * @author HUYNHDC
	 * @throws DataAccessException
	 */
	@Override
	public List<GroupCategorySearchVO> getListGroupCategoryParentByFilter(
			GroupCategorySearchFilter filter) throws DataAccessException {// TODO
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();

		selectSql.append(" SELECT gc.id  	as id, ");
		selectSql.append(" gc.name 			as name, ");
		selectSql.append(" gc.code 			as code, ");
		selectSql.append(" gc.description 	as description, ");
		selectSql.append(" gc.status 		as status, ");
		selectSql.append(" gc.media_id 		as mediaId, ");
		selectSql.append(" gc.parent_id 	as parentId, ");
		selectSql.append(" me.name 			as mediaName, ");
		selectSql.append(" gcp.name 		as parentName ");

		fromQuery.append(" FROM group_category gc ");
		fromQuery.append(" 		LEFT JOIN group_category gcp ");
		fromQuery.append(" 			ON gc.parent_id = gcp.id ");
		fromQuery.append(" 		LEFT JOIN Media me ");
		fromQuery.append(" 			ON gc.media_id = me.id ");

		fromQuery.append(" WHERE gc.status != ? ");
		paramSelect.add(GroupCategoryStatus.DELETED.getValue());
		countParams.add(GroupCategoryStatus.DELETED.getValue());

		if (filter.getId() != null) {
			fromQuery.append(" and gc.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}

		if (!StringUtil.isNullOrEmpty(filter.getNameText())) {
			fromQuery
					.append(" and (upper(gc.name_text) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText())
					+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText())
					+ "%");
		}

		if (filter.getCode() != null) {
			fromQuery.append(" and (upper(gc.code) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getCode())
					+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getCode())
					+ "%");
		}

		if (!StringUtil.isNullOrEmpty(filter.getDescription())) {
			fromQuery
					.append(" and (upper(gc.description) like upper(?) ESCAPE '/' )");
			paramSelect.add("%"
					+ StringUtil.toSearchText(filter.getDescription()) + "%");
			countParams.add("%"
					+ StringUtil.toSearchText(filter.getDescription()) + "%");
		}

		if (filter.getStatus() != null) {
			fromQuery.append(" and gc.status = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}

		if (filter.getMediaId() != null) {
			fromQuery.append(" and gc.media_id = ? ");
			paramSelect.add(filter.getMediaId());
			countParams.add(filter.getMediaId());
		}

		
		fromQuery.append(" and gc.parent_id is null ");
		
		
		selectSql.append(fromQuery).append(" ORDER BY gc.created_time DESC, gc.id DESC ");

		countQuery.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);

		String[] fieldNames = new String[] { "id", "name", "code",
				"description", "status", "mediaId", "parentId", "mediaName",
				"parentName" };

		Type[] fieldTypes = new Type[] { StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.STRING, StandardBasicTypes.INTEGER,
				StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING 
				};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(GroupCategory.class);

		List<GroupCategorySearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					GroupCategorySearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(GroupCategorySearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}

		return list;
	}
	/**
	 * @author HUYNHDC
	 * @throws DataAccessException
	 */
	@Override
	public List<GroupCategorySearchVO> getListGroupCategoryChildByFilter(
			GroupCategorySearchFilter filter) throws DataAccessException {// TODO
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();

		selectSql.append(" SELECT gc.id  	as id, ");
		selectSql.append(" gc.name 			as name, ");
		selectSql.append(" gc.code 			as code, ");
		selectSql.append(" gc.description 	as description, ");
		selectSql.append(" gc.status 		as status, ");
		selectSql.append(" gc.media_id 		as mediaId, ");
		selectSql.append(" gc.parent_id 	as parentId, ");
		selectSql.append(" me.name 			as mediaName, ");
		selectSql.append(" gcp.name 		as parentName ");

		fromQuery.append(" FROM group_category gc ");
		fromQuery.append(" 		LEFT JOIN group_category gcp ");
		fromQuery.append(" 			ON gc.parent_id = gcp.id ");
		fromQuery.append(" 		LEFT JOIN Media me ");
		fromQuery.append(" 			ON gc.media_id = me.id ");

		fromQuery.append(" WHERE gc.status != ? ");
		paramSelect.add(GroupCategoryStatus.DELETED.getValue());
		countParams.add(GroupCategoryStatus.DELETED.getValue());

		if (filter.getId() != null) {
			fromQuery.append(" and gc.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}

		if (!StringUtil.isNullOrEmpty(filter.getNameText())) {
			fromQuery
					.append(" and (upper(gc.name_text) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText())
					+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText())
					+ "%");
		}

		if (filter.getCode() != null) {
			fromQuery.append(" and (upper(gc.code) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getCode())
					+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getCode())
					+ "%");
		}

		if (!StringUtil.isNullOrEmpty(filter.getDescription())) {
			fromQuery
					.append(" and (upper(gc.description) like upper(?) ESCAPE '/' )");
			paramSelect.add("%"
					+ StringUtil.toSearchText(filter.getDescription()) + "%");
			countParams.add("%"
					+ StringUtil.toSearchText(filter.getDescription()) + "%");
		}

		if (filter.getStatus() != null) {
			fromQuery.append(" and gc.status = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}

		if (filter.getMediaId() != null) {
			fromQuery.append(" and gc.media_id = ? ");
			paramSelect.add(filter.getMediaId());
			countParams.add(filter.getMediaId());
		}
		if (filter.getParentId() != null) {
			fromQuery.append(" and gc.parent_id = ? ");
			paramSelect.add(filter.getParentId());
			countParams.add(filter.getParentId());
		}
		
		fromQuery.append(" and gc.parent_id is not null ");
		
		
		selectSql.append(fromQuery).append(" ORDER BY gc.created_time DESC , gc.id DESC ");

		countQuery.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);

		String[] fieldNames = new String[] { "id", "name", "code",
				"description", "status", "mediaId", "parentId", "mediaName",
				"parentName" };

		Type[] fieldTypes = new Type[] { StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.STRING, StandardBasicTypes.INTEGER,
				StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING };

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(GroupCategory.class);

		List<GroupCategorySearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					GroupCategorySearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(GroupCategorySearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}

		return list;
	}
	
	@Override
	public List<GroupCategory> getGroupCategoryNotParent()
			throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * ");
		sql.append("FROM group_category ");
		sql.append("WHERE parent_id IS NOT NULL ");
		// sql.append("AND gc1.id NOT      IN ");
		// sql.append("  (SELECT gc2.parent_id FROM group_category gc2 ");
		// sql.append("  where parent_id is not null) ");
		sql.append("AND status != -1 ");
		sql.append("ORDER BY name ");
		return repo.getListBySQL(GroupCategory.class, sql.toString(), null);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.viettel.core.dao.GroupCategoryDAO#getListGroupCategory(int)
	 */
	@Override
	public List<GroupCategoryVO> getListGroupCategory(int groupCategoryId)
			throws DataAccessException {
		StringBuilder SQL = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		SQL.append("SELECT id, \n");
		SQL.append("  name, \n");
		SQL.append("  (SELECT listagg(TO_CHAR(GG.id \n");
		SQL.append("    ||',' \n");
		SQL.append("    ||GG.name), ';') within GROUP ( \n");
		SQL.append("  ORDER BY GG.id) \n");
		SQL.append("  FROM GROUP_CATEGORY GG \n");
		SQL.append("  WHERE GG.PARENT_ID=G.ID \n");
		SQL.append("  ) AS childrentLst \n");
		SQL.append("FROM GROUP_CATEGORY G \n");
		SQL.append("WHERE G.parent_id IS NULL \n");
		if (groupCategoryId != 0) {
			SQL.append("AND G.id = ? \n");
			params.add(groupCategoryId);
		}
		SQL.append("ORDER BY id ");
		
		
		String[] fieldNames = new String[] { "id", "name", "childrentLst" };

		Type[] fieldTypes = new Type[] { StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(GroupCategory.class);
		
		
		List<GroupCategoryVO> list;
		list = repo.getListByQueryAndScalar(GroupCategoryVO.class,
				fieldNames, fieldTypes, SQL.toString(), params,
				synchronizedClass);
		if(list.size()>0){
			for (GroupCategoryVO VOEx : list) {
				VOEx.setLstCategory(VOEx.getChildrentLst());
			}
		}
		return list;
	}
	@Override
	public List<GroupCategory> getGroupCategoryParent()
			throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * ");
		sql.append("FROM group_category ");
		sql.append("WHERE parent_id IS NULL ");
		// sql.append("AND gc1.id NOT      IN ");
		// sql.append("  (SELECT gc2.parent_id FROM group_category gc2 ");
		// sql.append("  where parent_id is not null) ");
		sql.append("AND status != -1 ");
		sql.append("ORDER BY name ");
		return repo.getListBySQL(GroupCategory.class, sql.toString(), null);
	}
	@Override
	public Boolean checkNameExist(String userName) throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		sql.append(" select count(1) count from dual where exists (select 1 from GROUP_CATEGORY where Upper(NAME_TEXT) = ? and status != ?)");
		params.add(userName.toUpperCase());
		params.add(GroupCategoryStatus.DELETED.getValue());
		int c = repo.countBySQL(sql.toString(), params);
		return c > 0 ? true : false;
	} 
}
